﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;

namespace hello_accinv
{
    public partial class Mainform : Form
    {
        private Database db;
        private const int reStockPoint = 5;
        private string loginUser = "User";

        public Mainform()
        {
            InitializeComponent();
            db = new Database();
            if (!db.TestConnection())
            {
                System.Environment.Exit(1);
            }
        }

        public void SetPermission(int permission)
        {
            switch (permission)
            {
                case 1: // Manager
                    recreateDatabaseToolStripMenuItem.Visible = false;
                    toolStripSeparator1.Visible = false;
                    break;
                case 2: // Stock manager
                    administrativeToolsToolStripMenuItem.Visible = false;
                    tabControl1.TabPages.Remove(tabSale);
                    tabControl1.TabPages.Remove(tabHistory);
                    break;
                case 3: // Sale employee
                    administrativeToolsToolStripMenuItem.Visible = false;
                    tabControl1.TabPages.Remove(tabHistory);
                    btnAddNewProduct.Visible = false;
                    btnEditProduct.Visible = false;
                    btnDeleteProduct.Visible = false;
                    btnManageCategory.Visible = false;
                    btnAddtoStock.Location = new Point(23, 29);
                    break;
            }
        }

        private void btnAddNewProduct_Click(object sender, EventArgs e)
        {
            AddProduct ap = new AddProduct();
            ap.loginUser = loginUser;
            ap.ShowDialog();
            ap.Dispose();
            LoadTableProduct();
        }

        private void Mainform_Load(object sender, EventArgs e)
        {
            LoadTableProduct();
            LoadCategoryList();

            radioReportToday.Text = "Today  (" + DateTime.Today.ToLongDateString() + ")";
            dtpickerReportDateFrom.MaxDate = DateTime.Today;
            dtpickerReportDateTo.MaxDate = DateTime.Today;

            txtboxSalePCode.Focus();
        }

        private void LoadTableProduct()
        {
            datagridProduct.DataSource = 
                db.Select("P.PCode AS PCode, P.Name AS PName, P.UnitPrice AS PPrice, P.Quantity AS PQuantity, P.Detail AS PDetail, P.Remark AS PRemark, C.Name AS CName, C.Unit AS CUnit",
                          "Product P INNER JOIN Category C ON P.CategoryID = C.CategoryID",
                          "Flag = 1");
            datagridProduct.ClearSelection();
        }

        private void LoadTableProduct(string condition)
        {
            datagridProduct.DataSource =
                db.Select("P.PCode AS PCode, P.Name AS PName, P.UnitPrice AS PPrice, P.Quantity AS PQuantity, P.Detail AS PDetail, P.Remark AS PRemark, C.Name AS CName, C.Unit AS CUnit",
                          "Product P INNER JOIN Category C ON P.CategoryID = C.CategoryID",
                          condition);
            datagridProduct.ClearSelection();
        }

        private void btnDeleteProduct_Click(object sender, EventArgs e)
        {
            if (datagridProduct.RowCount > 0)
            {
                foreach (DataGridViewRow dr in datagridProduct.SelectedRows)
                {
                    if (MessageBox.Show("Code\t" + dr.Cells["PCode"].Value + "\nName\t" + dr.Cells["PName"].Value + "\nCategory\t" + dr.Cells["PCategory"].Value + "\n\nDo you want to delete this product?", 
                        "Confirmation", 
                        MessageBoxButtons.YesNo, 
                        MessageBoxIcon.Exclamation) == DialogResult.Yes)
                    {
                        db.Update("Product", "Flag = 0", "PCode = '" + dr.Cells["PCode"].Value + "'");
                        db.Insert("ActionLog", "'" + CurrentDateTime.GetDate() + "','" + CurrentDateTime.GetTime() + "','" + loginUser + " deleted product " + dr.Cells["PName"].Value + ".'");
                   }
                }
                LoadTableProduct();
            }
            datagridProduct.ClearSelection();
        }

        private void datagridProduct_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e)
        {
            if (datagridProduct.SelectedRows.Count == 0)
            {
                btnEditProduct.Enabled = false;
                btnDeleteProduct.Enabled = false;
            }
            else
            {
                btnEditProduct.Enabled = true;
                btnDeleteProduct.Enabled = true;
            }
            ReRunNumber(datagridProduct);
        }

        private void cmbCategoryFilter_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbCategoryFilter.Enabled && cmbCategoryFilter.SelectedIndex != -1)
            {
                String condition;
                DataTable dt = db.Select("CategoryID", "Category", "Name = '" + cmbCategoryFilter.SelectedItem.ToString() + "'");
                condition = "Flag = 1 AND C.CategoryID = '" + dt.Rows[0]["CategoryID"].ToString() + "'";
                LoadTableProduct(condition);
            }
        }

        private void LoadCategoryList()
        {
            DataTable dt = db.Select("Name", "Category", "1 ORDER BY Name ASC");
            List<string> lst = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                lst.Add(dt.Rows[i]["Name"].ToString());
            }
            cmbCategoryFilter.DataSource = lst;
            cmbCategoryFilter.SelectedIndex = -1;
        }

        private void radioFilterCategory_CheckedChanged(object sender, EventArgs e)
        {
            cmbCategoryFilter.SelectedIndex = -1;
            if (radioFilterCategory.Checked)
            {
                cmbCategoryFilter.Enabled = true;
            }
            else
            {
                cmbCategoryFilter.Enabled = false;
            }
        }

        private void radioShowAll_CheckedChanged(object sender, EventArgs e)
        {
            if (radioShowAll.Checked)
            {
                LoadTableProduct();
            }
        }

        private void btnEditProduct_Click(object sender, EventArgs e)
        {
            LoadEditProductDialog();
        }

        private void btnManageCategory_Click(object sender, EventArgs e)
        {
            ManageCategory mc = new ManageCategory();
            mc.loginUser = loginUser;
            mc.ShowDialog();
            mc.Dispose();
            LoadCategoryList();
        }

        private void RecreateDatabase()
        {
            MySqlConnection conn = new MySqlConnection("Server=localhost;" +
                                                        "Uid=root;" +
                                                        "Pwd=;" +
                                                        "CharSet=utf8;" +
                                                        "Allow Zero Datetime=True;");
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            conn.Open();
            cmd.CommandText = "DROP DATABASE accinv";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "SET NAMES UTF8";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE DATABASE accinv CHARACTER SET utf8 COLLATE utf8_general_ci";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "USE accinv";
            cmd.ExecuteNonQuery();

            string[] tableList = hello_accinv.Properties.Resources.DML.Split(';');
            foreach (string table in tableList)
            {
                if (!table.Equals(""))
                {
                    cmd.CommandText = table;
                    cmd.ExecuteNonQuery();
                }
            }
            MessageBox.Show("Finish");
        }

        private void btnAddtoStock_Click(object sender, EventArgs e)
        {
            DataTable dt = db.Select("*", "Product", "1");
            if (dt.Rows.Count > 0)
            {
                AddStock ads = new AddStock();
                ads.loginUser = loginUser;
                ads.ShowDialog();
                ads.Dispose();
                LoadTableProduct();
            }
            else
            {
                if (MessageBox.Show("No product in stock. Add product first, Add now ?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == System.Windows.Forms.DialogResult.Yes)
                {
                    btnAddNewProduct_Click(sender, e);
                }
            }
        }

        private bool IsNumeric(string input)
        {
            string validChars = "0123456789.";
            char inputChar;
            bool result = true;
            for (int i = 0; i < input.Length; i++)
            {
                inputChar = input[i];
                if (validChars.IndexOf(inputChar) == -1)
                {
                    result = false;
                    break;
                }
            }
            return result;
        }

        private string ToLongBaht(String value)
        {
            string longBaht = "";
            string preBaht = "";
            string postBaht = "";

            string[] th = new String[] { "ล้าน", "แสน", "หมื่น", "พัน", "ร้อย", "สิบ" };
            string[] thaiDigit = new String[] { "ศูนย์", "หนึ่ง", "สอง", "สาม", "สี่", "ห้า", "หก", "เจ็ด", "แปด", "เก้า" };

            string pre, post;
            pre = post = "";
            int ipre, ppre;
            ipre = ppre = 0;

            if (value.Contains("."))
            {
                pre = value.Substring(0, value.IndexOf('.'));
                post = value.Substring(value.IndexOf('.') + 1);
                if (post.Length > 2)
                {
                    Console.WriteLine("สตางค์ไม่ถูก");
                    return "";
                }
                ipre = Int32.Parse(pre);
                ppre = Int32.Parse(post);
            }
            else
            {
                pre = value;
                ipre = Int32.Parse(pre);
            }

            // บาท
            int start = 6;
            int mod = 10;
            for (int i = 0; i < (pre.Length); i++)
            {
                int index = (ipre % mod) / (mod / 10);
                if (i != 0)
                {
                    if ((index == 2) && (start == 6))
                    {
                        preBaht = "ยี่" + th[start - 1] + preBaht;
                    }
                    else if ((index == 1) && (start == 6))
                    {
                        preBaht = th[start - 1] + preBaht;
                    }
                    else if (index != 0)
                    {
                        preBaht = thaiDigit[index] + th[start - 1] + preBaht;
                    }
                    start--;
                    mod *= 10;
                }
                else
                {
                    if ((index == 1) && (ipre > 10))
                        preBaht = "เอ็ด" + preBaht;
                    else if (index != 0)
                        preBaht = thaiDigit[index] + preBaht;
                    mod *= 10;
                }
            }

            // สตางค์
            start = 6;
            mod = 10;
            for (int i = 0; i < (post.Length); i++)
            {
                int index = (ppre % mod) / (mod / 10);
                if (i != 0)
                {
                    if ((index == 2) && (start == 6))
                    {
                        postBaht = "ยี่" + th[start - 1] + postBaht;
                    }
                    else if ((index == 1) && (start == 6))
                    {
                        postBaht = th[start - 1] + postBaht;
                    }
                    else if (index != 0)
                    {
                        postBaht = thaiDigit[index] + th[start - 1] + postBaht;
                    }
                    start--;
                    mod *= 10;
                }
                else
                {
                    if ((index == 1) && (ppre > 10))
                        postBaht = "เอ็ด" + postBaht;
                    else if (index != 0)
                        postBaht = thaiDigit[index] + postBaht;
                    mod *= 10;
                }
            }

            if (pre.Length > 0)
                longBaht += preBaht + "บาท";
            if ((post.Length > 0) && (ppre > 0))
                longBaht += postBaht + "สตางค์";
            else
                longBaht += "ถ้วน";

            return longBaht;
        }

        private void Mainform_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (datagridSaleProductList.Rows.Count > 0)
            {
                if (MessageBox.Show("In progress, cancel?", "Cancel", 
                    MessageBoxButtons.YesNo, 
                    MessageBoxIcon.Asterisk) == System.Windows.Forms.DialogResult.No)
                {
                    e.Cancel = true;
                    return;
                }
            }
            if (DialogResult != System.Windows.Forms.DialogResult.Yes)
            {
                if (MessageBox.Show("Exit ?", "Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
                {
                    e.Cancel = true;
                }
                else
                {
                    DialogResult = System.Windows.Forms.DialogResult.No;
                }
            }
        }

        private void tabControl1_Selecting(object sender, TabControlCancelEventArgs e)
        {
            switch (e.TabPageIndex)
            {
                case 1:
                case 2:
                    if (datagridSaleProductList.Rows.Count > 0)
                    {
                        if (MessageBox.Show("In progress, Cancel?", 
                            "", 
                            MessageBoxButtons.YesNo, 
                            MessageBoxIcon.Warning) == DialogResult.Yes)
                        {
                            ClearTabSale();
                        }
                        else
                        {
                            e.Cancel = true;
                            txtboxSalePCode.Focus();
                        }
                    }
                    else
                    {
                        ClearTabSale();
                    }
                    cmbCategoryFilter.SelectedIndex = -1;
                    datagridProduct.ClearSelection();
                    break;
            }
        }

        private void btnDiscount_Click(object sender, EventArgs e)
        {
            LoadDiscountDialog();
        }

        private void ClearTabSale()
        {
            ClearDataGridView(datagridSaleProductList);
            txtboxSalePCode.Text = "";
            txtboxSalePCode.Focus();
        }

        private void ClearDataGridView(DataGridView dg)
        {
            int rowCount = dg.Rows.Count;
            for (int i = 0; i < rowCount; i++)
            {
                dg.Rows.RemoveAt(0);
            }
        }

        private void cmbReportType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbReportType.SelectedIndex != -1)
            {
                switch (cmbReportType.SelectedIndex)
                {
                    case 0: //Sale Report
                        dataGridView2.Visible = true;
                        dataGridView1.Visible = false;
                        panel1.Visible = true;
                        panel2.Visible = true;
                        LoadSaleReport();
                        break;
                    case 1: //Stock Report 
                        dataGridView1.Visible = true;
                        dataGridView2.Visible = false;
                        panel1.Visible = true;
                        panel2.Visible = false;
                        LoadStockReport();
                        break;
                }
            }
            else
            {
                dataGridView1.Visible = false;
                dataGridView2.Visible = false;
                panel1.Visible = false;
                panel2.Visible = false;
            }
        }

        private void LoadSaleReport() 
        {
            string condition = "";
            if (radioReportToday.Checked)
            {
                condition = "Date = '" + CurrentDateTime.GetDate() + "'";
            }
            else if (radioReportFromTo.Checked)
            {
                condition = "Date BETWEEN '" + ToSqlDate(dtpickerReportDateFrom.Value.ToShortDateString()) + "' AND '" + ToSqlDate(dtpickerReportDateTo.Value.ToShortDateString()) + "'";
            }
            else
            {
                condition = "1";
            }

            dataGridView2.DataSource =
                db.Select("S.Date, S.Time, P.PCode, P.Name, P.UnitPrice, PS.Discount, PS.Quantity, PS.TotalPrice, PS.Remark",
                          "Sale S INNER JOIN (ProductSale PS INNER JOIN Product P ON PS.ProductCode = P.PCode) ON S.SaleID = PS.SaleID",
                          condition);
            dataGridView2.ClearSelection();

            DataTable dt;
            if (dataGridView2.Rows.Count > 0)
            {
                chkboxReportFilterProduct.Enabled = true;
                dt = db.Select("DISTINCT P.Name",
                                        "Sale S INNER JOIN (ProductSale PS INNER JOIN Product P ON PS.ProductCode = P.PCode) ON S.SaleID = PS.SaleID",
                                        condition);
                List<string> lst = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    lst.Add(dt.Rows[i]["Name"].ToString());
                }
                cmbReportFilterProduct.DataSource = lst;
                cmbReportFilterProduct.SelectedIndex = -1;
            }
            else
            {
                chkboxReportFilterProduct.Enabled = false;
            }
            
            dt = db.Select("TotalPrice, Discount", "Sale", condition);
            decimal totalPrice, totalDiscount; totalPrice = totalDiscount = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                totalPrice += Decimal.Parse(dt.Rows[i]["TotalPrice"].ToString());
                totalDiscount += Decimal.Parse(dt.Rows[i]["Discount"].ToString());
            }
            textBox1.Text = (totalPrice - totalDiscount).ToString("#,#0.00#");
            textBox2.Text = totalPrice.ToString("#,#0.00#");
            textBox3.Text = totalDiscount.ToString("#,#0.00#");
        }

        private void LoadStockReport() 
        {
            string condition = "";
            if (radioReportToday.Checked)
            {
                condition = "Date = '" + CurrentDateTime.GetDate() + "'";
            }
            else if (radioReportFromTo.Checked)
            {
                condition = "Date BETWEEN '" + ToSqlDate(dtpickerReportDateFrom.Value.ToShortDateString()) + "' AND '" + ToSqlDate(dtpickerReportDateTo.Value.ToShortDateString()) + "'";
            }
            else
            {
                condition = "1";
            }

            dataGridView1.DataSource =
                db.Select("Date, Time, PCode, Name, Purchase.Quantity, Remark",
                          "Purchase INNER JOIN Product ON Purchase.ProductCode = Product.PCode",
                          condition);
            dataGridView1.ClearSelection();

            DataTable dt;

            if (dataGridView1.Rows.Count > 0)
            {
                chkboxReportFilterProduct.Enabled = true;
                dt = db.Select("DISTINCT Name",
                                        "Purchase INNER JOIN Product ON Purchase.ProductCode = Product.PCode",
                                        condition);
                List<string> lst = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    lst.Add(dt.Rows[i]["Name"].ToString());
                }
                cmbReportFilterProduct.DataSource = lst;
                cmbReportFilterProduct.SelectedIndex = -1;
            }
            else
            {
                chkboxReportFilterProduct.Enabled = false;
            }
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            cmbReportType.SelectedIndex = -1;
        }

        private void linklbChooseProduct_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            LoadChooseProductDialog();
        }

        private void radioReportToday_CheckedChanged(object sender, EventArgs e)
        {
            if (radioReportFromTo.Checked)
            {
                dtpickerReportDateFrom.Enabled = true;
                dtpickerReportDateTo.Enabled = true;

                if (cmbReportType.SelectedIndex != -1)
                {
                    switch (cmbReportType.SelectedIndex)
                    {
                        case 0: //Sale Report
                            LoadSaleReport();
                            break;
                        case 1: //Stock Report 
                            LoadStockReport();
                            break;
                    }
                }
            }
            else
            {
                dtpickerReportDateFrom.Enabled = false;
                dtpickerReportDateTo.Enabled = false;
            }
        }

        private void radioReportFromTo_CheckedChanged(object sender, EventArgs e)
        {
            if (cmbReportType.SelectedIndex != -1)
            {
                switch (cmbReportType.SelectedIndex)
                {
                    case 0: //Sale Report
                        LoadSaleReport();
                        break;
                    case 1: //Stock Report
                        LoadStockReport();
                        break;
                }
            }
        }

        private void dtpickerReportDateFrom_ValueChanged(object sender, EventArgs e)
        {
            if (radioReportFromTo.Checked)
            {
                if (cmbReportType.SelectedIndex != -1)
                {
                    switch (cmbReportType.SelectedIndex)
                    {
                        case 0: //Sale Report
                            LoadSaleReport();
                            break;
                        case 1: //Stock Report 
                            LoadStockReport();
                            break;
                    }
                }
            }
        }

        private void txtboxSalePCode_TextChanged(object sender, EventArgs e)
        {
            txtboxSalePCode.ForeColor = Color.Black;

            if (txtboxSalePCode.Text.Length == 6)
            {
                DataTable dt = db.Select("P.Name AS PN, C.Name AS CN, P.UnitPrice, Image, P.Quantity", 
                    "Product P INNER JOIN Category C ON P.CategoryID = C.CategoryID", 
                    "PCode = '" + txtboxSalePCode.Text + "' AND Flag = '1'");
                if (dt.Rows.Count == 1)
                {
                    if (Int32.Parse(dt.Rows[0]["Quantity"].ToString()) > 0)
                    {

                        txtboxName.Text = dt.Rows[0]["PN"].ToString();
                        txtboxCategory.Text = dt.Rows[0]["CN"].ToString();
                        txtboxPrice.Text = dt.Rows[0]["UnitPrice"].ToString();

                        //byte[] image = (byte[])dt.Rows[0]["Image"];
                        //if (image.Length > 0)
                        //{
                        //    try
                        //    {
                        //        Stream ms = new MemoryStream(image, 0, image.Length);
                        //        pictureBox1.Image = Image.FromStream(ms, true, false);
                        //    }
                        //    catch (ArgumentException ex)
                        //    {
                        //        Console.WriteLine(ex.Message);
                        //    }
                        //}

                        txtboxDiscount.Text = "0.00";
                        txtboxQuantity.Enabled = true;
                        btnDiscount.Enabled = true;
                        btnRemark.Enabled = true;
                        labelQty.ForeColor = Color.Red;
                        txtboxQuantity.BackColor = Color.LightSalmon;
                        txtboxQuantity.Focus();
                    }
                    else
                    {
                        MessageBox.Show("Product:\t" + dt.Rows[0]["PN"].ToString() + "\nCategory:\t" + dt.Rows[0]["CN"].ToString() + "\n\nNo product in stock", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        txtboxSalePCode.SelectAll();
                    }
                }
                else
                {
                    txtboxSalePCode.ForeColor = Color.Red;
                }
            }
            else
            {
                labelQty.ForeColor = Color.Black;
                txtboxQuantity.BackColor = Color.White;
                txtboxName.Text = "";
                txtboxCategory.Text = "";
                txtboxPrice.Text = "";
                txtboxDiscount.Text = "";
                txtboxQuantity.Text = "";
                txtboxRemark.Text = "";
                txtboxQuantity.Enabled = false;
                btnDiscount.Enabled = false;
                btnRemark.Enabled = false;
            }
        }

        private void LoadDiscountDialog()
        {
            Discount dc = new Discount();
            dc.txtboxPrice.Text = txtboxPrice.Text;
            dc.txtboxProduct.Text = txtboxName.Text;
            if (dc.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtboxDiscount.Text = dc.txtboxDiscountBaht.Text;
            }
            else
            {
                txtboxDiscount.Text = "0.00";
            }
            txtboxQuantity.Focus();
        }

        private void LoadEditProductDialog()
        {
            EditProduct edProduct = new EditProduct();
            edProduct.loginUser = loginUser;
            edProduct.txtboxPCode.Text = datagridProduct.SelectedRows[0].Cells["PCode"].Value.ToString();
            edProduct.txtboxName.Text = datagridProduct.SelectedRows[0].Cells["PName"].Value.ToString();
            edProduct.txtboxUnitPrice.Text = datagridProduct.SelectedRows[0].Cells["PPrice"].Value.ToString();
            edProduct.txtboxQty.Text = datagridProduct.SelectedRows[0].Cells["PQty"].Value.ToString();
            edProduct.txtboxMoreDetails.Text = datagridProduct.SelectedRows[0].Cells["PDetail"].Value.ToString();

            DataTable dt = db.Select("Remark", "Purchase", "ProductCode = '" + edProduct.txtboxPCode.Text + "'");
            edProduct.txtboxRemark.Text = dt.Rows[0]["Remark"].ToString();

            if (edProduct.ShowDialog() == DialogResult.OK)
            {
                MessageBox.Show("Update !", "Complete");
            }
            LoadTableProduct();
            edProduct.Dispose();
        }
        
        private void txtboxQuantity_KeyUp(object sender, KeyEventArgs e)
        {
            switch (e.KeyCode)
            {
                case Keys.F1:
                    txtboxSalePCode.Focus();
                    break;
                case Keys.F2:
                    LoadChooseProductDialog();
                    break;
                case Keys.F3:
                    LoadDiscountDialog();
                    break;
                case Keys.F5:  
                    AddProductToList();
                    break;
            }
        }

        private void txtboxQuantity_ValueChanged(object sender, EventArgs e)
        {
            if (txtboxQuantity.Text.Length > 0 && IsNumeric(txtboxQuantity.Text))
            {
                btnAddToList.Enabled = true;
            }
            else
            {
                txtboxQuantity.Text = "";
                btnAddToList.Enabled = false;
            }
        }

        private void AddProductToList()
        {
            if (txtboxQuantity.Text != "" && CheckQuantity())
            {
                decimal totalPrice = Decimal.Parse(txtboxQuantity.Text) * (Decimal.Parse(txtboxPrice.Text) - Decimal.Parse(txtboxDiscount.Text));

                datagridSaleProductList.Rows.Add(new Object[] { datagridSaleProductList.Rows.Count + 1, txtboxSalePCode.Text, txtboxName.Text, Int32.Parse(txtboxQuantity.Text), txtboxPrice.Text, txtboxDiscount.Text, totalPrice,txtboxRemark.Text });
                datagridSaleProductList.ClearSelection();
                txtboxSalePCode.Text = "";
            }
            else
                MessageBox.Show("Check Quantity");
        }

        private void UpdateCostPanel()
        {
            decimal totalPrice, totalDiscount, totalVAT;
            totalPrice = totalDiscount = totalVAT = 0;

            for (int i = 0; i < datagridSaleProductList.Rows.Count; i++)
            {
                totalDiscount += Decimal.Parse(datagridSaleProductList.Rows[i].Cells[5].Value.ToString());
                totalPrice += Decimal.Parse(datagridSaleProductList.Rows[i].Cells[6].Value.ToString());
            }
            totalVAT = 0.07M * totalPrice;

            txtboxTotalPrice.Text = totalPrice.ToString("#,#0.00#");
            txtboxTotalDiscount.Text = totalDiscount.ToString("#,#0.00#");
            txtboxTotalVAT.Text = totalVAT.ToString("#,#0.00#");
        }

        private void btnCancelSale_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Clear?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
            {
                ClearTabSale();
            }
        }

        private void datagridSaleProductList_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 8)
            {
                DeleteSaleProductAtRow(e.RowIndex);
            }
        }

        private void DeleteSaleProductAtRow(int index)
        {
            if (MessageBox.Show("Delete?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
            {
                datagridSaleProductList.Rows.RemoveAt(index);
                ReRunNumber(datagridSaleProductList);
            }
        }

        private void ReRunNumber(DataGridView dg)
        {
            int number = 1;
            for (int i = 0; i < dg.Rows.Count; i++)
            {
                dg.Rows[i].Cells[0].Value = number++;
            }
        }

        private void btnAddToList_Click(object sender, EventArgs e)
        {
            AddProductToList();
        }

        private void LoadChooseProductDialog()
        {
            ChooseProduct cp = new ChooseProduct();
            if (cp.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtboxSalePCode.Text = cp.selectedProductCode;
            }
        }

        private void txtboxSalePCode_KeyUp(object sender, KeyEventArgs e)
        {
            switch (e.KeyCode)
            {
                case Keys.F2:
                    LoadChooseProductDialog();
                    break;
                case Keys.F8:
                    LoadSalePaymentDialog();
                    break;
            }
        }

        private void datagridSaleProductList_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e)
        {
            UpdateCostPanel();
            if (datagridSaleProductList.Rows.Count > 0)
            {
                btnSalePayment.Enabled = true;
                btnCancelSale.Enabled = true;
            }
            else
            {
                btnSalePayment.Enabled = false;
                btnCancelSale.Enabled = false;
            }
        }

        private void btnSalePayment_Click(object sender, EventArgs e)
        {
            LoadSalePaymentDialog();
        }

        private void LoadSalePaymentDialog()
        {
            if (MessageBox.Show("Confirm?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
            {
                Payment py = new Payment();
                py.txtboxTotalAmount.Text = txtboxTotalPrice.Text;
                if (py.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    int newSaleID = py.newSaleID;
                    for (int i = 0; i < datagridSaleProductList.Rows.Count; i++)
                    {
                        // Add ProductSale record (loop)
                        db.Insert("ProductSale", "'" + newSaleID + "','" + datagridSaleProductList.Rows[i].Cells[1].Value + "','" + datagridSaleProductList.Rows[i].Cells[3].Value + "','" + datagridSaleProductList.Rows[i].Cells[4].Value + "','" + datagridSaleProductList.Rows[i].Cells[5].Value + "','" + datagridSaleProductList.Rows[i].Cells[6].Value + "','" + datagridSaleProductList.Rows[i].Cells[7].Value + "'");
                        // Update stock quantity (loop)
                        db.Update("Product", "Quantity = Quantity - '" + datagridSaleProductList.Rows[i].Cells[3].Value + "'", "PCode = '" + datagridSaleProductList.Rows[i].Cells[1].Value + "'");
                        // Update discount
                        db.Update("Sale", "Discount = Discount + '" + datagridSaleProductList.Rows[i].Cells[5].Value + "'", "SaleID = '" + newSaleID + "'");
                    }
                    // Clear all
                    ClearTabSale();
                    txtboxSalePCode.Focus();
                }
                py.Dispose();
            }
        }

        private bool CheckQuantity()
        {
            int qty = Int32.Parse(txtboxQuantity.Text);
                if (qty == 0) return false;

            Int32 totalQty = 0;
            for(int i=0; i<datagridSaleProductList.RowCount; i++)
            {
                if (datagridSaleProductList.Rows[i].Cells["ColCode"].Value.ToString().Equals(txtboxSalePCode.Text))
                {
                    totalQty += Int32.Parse(datagridSaleProductList.Rows[i].Cells["ColQty"].Value.ToString());
                }
            }

            DataTable dt = db.Select("Quantity", "Product", "PCode = '" + txtboxSalePCode.Text + "'");
            if (dt.Rows.Count > 0)
            {
                if (qty > Int32.Parse(dt.Rows[0]["Quantity"].ToString()) || 
                    qty > (Int32.Parse(dt.Rows[0]["Quantity"].ToString()) - totalQty))
                {
                    return false;
                }
            }
            else
            {
                MessageBox.Show("Cannot retrieve product quantity", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

            if (Int32.Parse(dt.Rows[0]["Quantity"].ToString()) < reStockPoint)
            {
                MessageBox.Show("Need to restock!");
            }
            return true;
        }

        private void dtpickerReportDateTo_ValueChanged(object sender, EventArgs e)
        {
            if (radioReportFromTo.Checked)
            {
                if (cmbReportType.SelectedIndex != -1)
                {
                    switch (cmbReportType.SelectedIndex)
                    {
                        case 0: //Sale Report
                            LoadSaleReport();
                            break;
                        case 1: //Stock Report 
                            LoadStockReport();
                            break;
                    }
                }
            }
        }

        private void chkboxReportFilterProduct_CheckedChanged(object sender, EventArgs e)
        {
            if (chkboxReportFilterProduct.Checked)
            {
                cmbReportFilterProduct.Enabled = true;
            }
            else
            {
                cmbReportFilterProduct.SelectedIndex = -1;
                cmbReportFilterProduct.Enabled = false;
                if (cmbReportType.SelectedIndex != -1)
                {
                    switch (cmbReportType.SelectedIndex)
                    {
                        case 0: //Sale Report
                            LoadSaleReport();
                            break;
                        case 1: //Stock Report
                            LoadStockReport();
                            break;
                    }
                }
            }
        }

        private void cmbReportFilterProduct_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (chkboxReportFilterProduct.Checked && cmbReportFilterProduct.SelectedIndex != -1)
            {
                string selectedName = cmbReportFilterProduct.SelectedItem.ToString();
                //switch (cmbReportType.SelectedIndex)
                //{
                //    case 0: //Sale Report
                //        foreach (DataGridViewRow dr in dataGridView2.Rows)
                //        {
                //            if (!dr.Cells[3].Value.ToString().Equals(selectedName))
                //            {
                //                dataGridView2.Rows.Remove(dr);
                //            }
                //        }
                //        break;
                //    case 1: //Stock Report
                //        foreach (DataGridViewRow dr in dataGridView1.Rows)
                //        {
                //            if (!dr.Cells[3].Value.ToString().Equals(selectedName))
                //            {
                //                dataGridView1.Rows.Remove(dr);
                //            }
                //        }
                //        break;
                //}
            }
        }

        private void logoutToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (datagridSaleProductList.Rows.Count > 0)
            {
                if (MessageBox.Show("In progress, cancel?", "Cancel",
                    MessageBoxButtons.YesNo,
                    MessageBoxIcon.Asterisk) == System.Windows.Forms.DialogResult.No)
                {
                    return;
                }
            }
            if (MessageBox.Show("Logout ?", "Logout", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
            {
                DoLogout();
                DialogResult = System.Windows.Forms.DialogResult.Yes;
                Dispose();
            }
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (datagridSaleProductList.Rows.Count > 0)
            {
                if (MessageBox.Show("In progress, cancel?", "Cancel",
                    MessageBoxButtons.YesNo,
                    MessageBoxIcon.Asterisk) == System.Windows.Forms.DialogResult.No)
                {
                    return;
                }
            }
            if (MessageBox.Show("Exit ?", "Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
            {
                DoLogout();
                DialogResult = System.Windows.Forms.DialogResult.No;
                Dispose();
            }
        }

        private void viewLogToolStripMenuItem_Click(object sender, EventArgs e)
        {
            ViewLog vl = new ViewLog();
            vl.ShowDialog();
            vl.Dispose();
        }

        private void recreateDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Recreate database?", "Database", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                RecreateDatabase();
            }
        }

        private void manageUsersToolStripMenuItem_Click(object sender, EventArgs e)
        {
            ManageUser mu = new ManageUser();
            mu.ShowDialog();
            mu.Dispose();
        }

        private void btnRemark_Click(object sender, EventArgs e)
        {
            AddRemark AdRemark = new AddRemark();
            if (AdRemark.ShowDialog() == DialogResult.OK)
            {
                txtboxRemark.Text = AdRemark.textBox1.Text;
            }
            txtboxQuantity.Focus();
        }

        bool RegExFormatNumber(string text)
        {
            Regex rEx = new Regex("^[1-9][0-9]+((.)[0-9]+)?$");
            return rEx.IsMatch(text);
        }

        private void DoLogout()
        {
            db.Insert("ActionLog", "'" + CurrentDateTime.GetDate() + "','" + CurrentDateTime.GetTime() + "','User logged out.'");
        }

        private string ToSqlDate(string input)
        {
            string[] split = input.Split('/');
            return (Int32.Parse(split[2]) - 543) + "-" + ((split[1].Length == 1) ? "0" : "") + split[1] + "-" + ((split[0].Length == 1) ? "0" : "") + split[0];
        }
    }
}
